package cn.com.generaldata.os.service.common;

import java.io.File;
import java.io.FileOutputStream;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.HashSet;
import java.util.List;
import java.util.Map;
import java.util.Map.Entry;
import java.util.Set;
import java.util.TreeSet;

import javax.annotation.PostConstruct;

import org.apache.commons.lang3.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.util.CellRangeAddress;
import org.nutz.castor.Castors;
import org.nutz.dao.Cnd;
import org.nutz.dao.Dao;
import org.nutz.dao.entity.Record;
import org.nutz.dao.impl.sql.SqlTemplate;
import org.nutz.el.El;
import org.nutz.lang.Files;
import org.nutz.lang.Lang;
import org.nutz.lang.Mirror;
import org.nutz.lang.Streams;
import org.nutz.lang.util.Context;
import org.nutz.log.Log;
import org.nutz.log.Logs;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.stereotype.Service;

import cn.com.generaldata.jsme_indicator_system.entity.Indicator;
import cn.com.generaldata.jsme_indicator_system.entity.IndicatorInfo;
import cn.com.generaldata.jsme_indicator_system.entity.ReportInputTitle;
import cn.com.generaldata.jsme_indicator_system.entity.ReportTypeIndicator;
import cn.com.generaldata.jsme_indicator_system.entity.ReportTypeNz;
import cn.com.generaldata.jsme_indicator_system.vo.reportdesigner.Report;
import cn.com.generaldata.jsme_indicator_system.vo.reportdesigner.ReportBodyCell;
import cn.com.generaldata.jsme_indicator_system.vo.reportdesigner.ReportCell;
import cn.com.generaldata.jsme_indicator_system.vo.reportdesigner.ReportFooterCell;
import cn.com.generaldata.jsme_indicator_system.vo.reportdesigner.ReportTemplateInfo;
import cn.com.generaldata.jsme_indicator_system.vo.reportdesigner.ReportTitleCell;
import cn.com.generaldata.os.pojo.ReportDetail;

import com.google.common.collect.ArrayListMultimap;
import com.google.common.collect.Maps;
import com.google.common.collect.Multimap;
import com.google.common.collect.Sets;
import com.google.common.collect.SortedMultiset;
import com.google.common.collect.Table;
import com.google.common.collect.TreeBasedTable;
import com.google.common.collect.TreeMultiset;

/**
 * 报表设计生成Excel模板.
 */
@Service
public class ReportExcelTemplateBuilder {

	private static final Log logger = Logs.get();

	private static final String HEAD_STYLE_NAME = "header";
	private static final String TEXT_STYLE_NAME = "text";
	private static final String INDICATOR_STYLE_NAME = "indicator";

	/** 默认列宽 */
	private static final int DEFAULT_COLUMN_WIDTH = 60;
	/** 默认行高 */
	private static final int DEFAULT_ROW_HEIGHT = 25;

	@Autowired
	private Dao dao;
	@Autowired
	private SqlTemplate sqlTemplate;
	// 存储Excel文件的目录
	@Value("excel.file.template.save.dir")
	private String saveDir;

	// 预览Excel，存储Excel文件的目录
	@Value("excel.file.template.preview.save.dir")
	private String previewSaveDir;

	// 方便取得每行，每列的，高宽做本地线程变量
	private final ThreadLocal<Map<Integer, Integer>> ctx = new ThreadLocal<Map<Integer, Integer>>();

	private final Mirror<ReportTypeNz> mirror = Mirror.me(ReportTypeNz.class);
	private final Mirror<cn.com.generaldata.os.pojo.Report> mirrorReport = Mirror
			.me(cn.com.generaldata.os.pojo.Report.class);
	private final Mirror<ReportDetail> mirrorReportDetail = Mirror.me(ReportDetail.class);
	private Map<String, String> unitCodeMap;
	private final SimpleDateFormat formatter = new SimpleDateFormat("yyyy-MM-dd");

	/**
	 * 初始化方法，清空预览目录的Excel文件.
	 */
	@PostConstruct
	public void init() {
		Files.clearDir(new File(previewSaveDir));

		List<Record> records = dao.query("T_SYSTEM_CODEDIC", Cnd.where("SECTION", "=", "10022"));

		unitCodeMap = Maps.newHashMap();
		for (Record record : records) {
			unitCodeMap.put(record.getString("CODE"), record.getString("CODE_NAME"));
		}
	}

	/**
	 * 生成报表的Excel文件.
	 * 
	 * @param reportTemplateInfo 报表信息对象
	 * @return Excel文件
	 */
	public File buildReportExcelTemplate(Report reportTemplateInfo) {
		return buildReportExcelTemplate(reportTemplateInfo, saveDir, null);
	}

	/**
	 * 预览功能生成报表的Excel文件.
	 * 
	 * @param reportTemplateInfo 报表信息对象
	 * @return Excel文件
	 */

	public File previewReportExcelTemplate(Report reportTemplateInfo) {
		return buildReportExcelTemplate(reportTemplateInfo, previewSaveDir, null);
	}

	/**
	 * 构造报表的Excel文件.
	 * 
	 * @param reportTemplateInfo 报表设计器对象信息
	 * @param savePath 存储目录
	 * @return 生成的Excel文件 File对象
	 */
	public File buildReportExcelTemplate(Report reportTemplateInfo, String savePath, String name) {
		File fileFolder = new File(savePath);
		if (!fileFolder.exists() || !fileFolder.isDirectory())
			fileFolder.mkdirs();
		File excelFile;
		if (name == null)
			excelFile = new File(String.format("%s/%s.xls", savePath, reportTemplateInfo.getReportTypeId()));
		else
			excelFile = new File(String.format("%s/%s.xls", savePath, name));

		FileOutputStream fileOut = null;
		try {
			fileOut = new FileOutputStream(excelFile);
			Workbook wb = new HSSFWorkbook();
			Map<String, CellStyle> styles = createStyles(wb);
			Sheet sheet = wb.createSheet();
			buildReportHead(sheet, reportTemplateInfo);
			buildReportBody(sheet, reportTemplateInfo, styles);
			buildReportFoot(sheet, reportTemplateInfo);
			mergeCells(sheet, reportTemplateInfo);
			setSheetColumnAutoWidth(sheet, reportTemplateInfo);
			wb.write(fileOut);
			fileOut.close();
		} catch (Exception e) {
			logger.error(String.format("创建Excel模板文件发生错误,报表Id：%s", reportTemplateInfo.getReportTypeId()), e);
			throw Lang.wrapThrow(e, "创建Excel模板文件发生错误");
		} finally {
			Streams.safeClose(fileOut);
			ctx.remove();
		}

		return excelFile;
	}

	/**
	 * 按照报表生成excel
	 * @param reportTemplateInfo
	 * @param savePath
	 * @param name
	 * @return
	 */
	public File buildReportExcel(Integer dataType, cn.com.generaldata.os.pojo.Report reportPo,
			ReportTemplateInfo reportTemplateInfo, String savePath, String name, String areaId) {
		File fileFolder = new File(savePath);
		if (!fileFolder.exists() || !fileFolder.isDirectory())
			fileFolder.mkdirs();
		File excelFile;
		if (name == null)
			excelFile = new File(String.format("%s/%s.xls", savePath, reportTemplateInfo.getReportTypeId()));
		else
			excelFile = new File(String.format("%s/%s.xls", savePath, name));

		FileOutputStream fileOut = null;
		try {
			fileOut = new FileOutputStream(excelFile);
			Workbook wb = new HSSFWorkbook();
			Map<String, CellStyle> styles = createStyles(wb);
			Sheet sheet = wb.createSheet();
			buildReportHeadNew(sheet, reportTemplateInfo, reportPo);
			buildReportBodyNew(sheet, reportTemplateInfo, styles, reportPo, dataType, areaId);
			buildReportFootNew(sheet, reportTemplateInfo, reportPo, dataType);
			mergeCellsNew(sheet, reportTemplateInfo, reportPo, dataType);
			setSheetColumnAutoWidth(sheet, reportTemplateInfo);
			wb.write(fileOut);
			fileOut.close();
		} catch (Exception e) {
			logger.error(String.format("创建Excel模板文件发生错误,报表Id：%s", reportTemplateInfo.getReportTypeId()), e);
			throw Lang.wrapThrow(e, "创建Excel模板文件发生错误");
		} finally {
			Streams.safeClose(fileOut);
			ctx.remove();
		}

		return excelFile;
	}

	/**
	 * 设计每列的宽度.
	 * 
	 * @param sheet
	 * @param report
	 */
	private void setSheetColumnAutoWidth(Sheet sheet, Report report) {
		TreeSet<Short> rowColumns = Sets.newTreeSet();
		for (Row row : sheet) {
			rowColumns.add(row.getLastCellNum());
		}

		int mastColumns = rowColumns.last();
		for (int i = 0; i < mastColumns; i++) {
			sheet.setDefaultColumnWidth(15);
		}
	}

	/**
	 * 设计每列的宽度.
	 * 
	 * @param sheet
	 * @param reportTemplateInfo
	 */
	private void setSheetColumnAutoWidth(Sheet sheet, ReportTemplateInfo reportTemplateInfo) {
		TreeSet<Short> rowColumns = Sets.newTreeSet();
		for (Row row : sheet) {
			rowColumns.add(row.getLastCellNum());
		}

		int mastColumns = rowColumns.last();
		for (int i = 0; i < mastColumns; i++) {
			sheet.setDefaultColumnWidth(15);
		}

		// int[] widths = getColumnWidths(report);
		//
		// for (int i = 0; i < widths.length; i++) {
		// int width = widths[i];
		// sheet.setColumnWidth(i, (int) (width * 35.7));
		// }
	}

	/**
	 * 构造Excel表尾
	 * 
	 * @param sheet
	 * @param reportTemplateInfo
	 */
	private void buildReportFoot(Sheet sheet, Report reportTemplateInfo) {
		Table<Integer, Integer, ReportFooterCell> headTable = TreeBasedTable.create();
		for (ReportFooterCell cell : reportTemplateInfo.getUniqueReportFoots()) {
			headTable.put(cell.getRowid(), cell.getColid(), cell);
		}

		// Map<Integer, Integer> rowHeights = getRowHeights(report);
		for (Integer rowKey : headTable.rowKeySet()) {
			Map<Integer, ReportFooterCell> rowMap = headTable.row(rowKey);
			Row row = sheet.createRow(rowKey);
			row.setHeightInPoints(DEFAULT_ROW_HEIGHT);
			for (Entry<Integer, ReportFooterCell> entry : rowMap.entrySet()) {
				ReportFooterCell cell = entry.getValue();
				Cell excelCell = row.createCell(cell.getColid());
				if (ReportCell.CELL_TYPE_STATIC.equals(cell.getType())) {
					// 文本单元格
					excelCell.setCellValue(cell.getContent().replaceAll("&nbsp;", " "));
				} else {
					excelCell.setCellValue("");
				}
			}
		}

	}

	private void buildReportFootNew(Sheet sheet, ReportTemplateInfo reportTemplateInfo,
			cn.com.generaldata.os.pojo.Report reportPo, Integer dataType) {
		ReportTypeNz reportType = dao.fetch(ReportTypeNz.class, reportTemplateInfo.getReportTypeId());

		Table<Integer, Integer, ReportFooterCell> headTable = TreeBasedTable.create();
		for (ReportFooterCell cell : reportTemplateInfo.getUniqueReportFoots()) {
			headTable.put(cell.getRowid(), cell.getColid(), cell);
		}
		if (dataType == 1) {
			// Map<Integer, Integer> rowHeights = getRowHeights(report);
			for (Integer rowKey : headTable.rowKeySet()) {
				Map<Integer, ReportFooterCell> rowMap = headTable.row(rowKey);
				Row row = sheet.createRow(rowKey);
				row.setHeightInPoints(DEFAULT_ROW_HEIGHT);
				for (Entry<Integer, ReportFooterCell> entry : rowMap.entrySet()) {
					ReportFooterCell cell = entry.getValue();
					Cell excelCell = row.createCell(cell.getColid());
					if (ReportCell.CELL_TYPE_NONE.equals(cell.getType())) {
						excelCell.setCellValue("");
					} else if (ReportCell.CELL_TYPE_STATIC.equals(cell.getType())) {
						excelCell.setCellValue(cell.getContent().replaceAll("&nbsp;", " "));
					} else if (ReportCell.CELL_TYPE_INPUT.equals(cell.getType())) {
						setTitleExpCellValNew(cell, reportType, excelCell, reportPo);
					}
				}
			}
		} else if (dataType == 2) {
			Set<ReportDetail> reportDetails = reportPo.getReportDetails();
			for (Integer rowKey : headTable.rowKeySet()) {
				Map<Integer, ReportFooterCell> rowMap = headTable.row(rowKey);
				Row row = sheet.createRow(rowKey + reportDetails.size() - 1);
				row.setHeightInPoints(DEFAULT_ROW_HEIGHT);
				for (Entry<Integer, ReportFooterCell> entry : rowMap.entrySet()) {
					ReportFooterCell cell = entry.getValue();
					Cell excelCell = row.createCell(cell.getColid());
					if (ReportCell.CELL_TYPE_NONE.equals(cell.getType())) {
						excelCell.setCellValue("");
					} else if (ReportCell.CELL_TYPE_STATIC.equals(cell.getType())) {
						excelCell.setCellValue(cell.getContent().replaceAll("&nbsp;", " "));
					} else if (ReportCell.CELL_TYPE_INPUT.equals(cell.getType())) {
						setTitleExpCellValNew(cell, reportType, excelCell, reportPo);
					}
				}
			}
		}
	}

	/**
	 * 构造报表表格部分.
	 * 
	 * @param sheet
	 * @param reportTemplateInfo
	 */
	private void buildReportBody(Sheet sheet, Report reportTemplateInfo, Map<String, CellStyle> styles) {
		buildReportTableHead(sheet, reportTemplateInfo, styles);
		buildReportTableBody(sheet, reportTemplateInfo, styles);
	}

	/**
	 * 构造报表表格部分.
	 * 
	 * @param sheet
	 * @param reportTemplateInfo
	 */
	private void buildReportBodyNew(Sheet sheet, ReportTemplateInfo reportTemplateInfo, Map<String, CellStyle> styles,
			cn.com.generaldata.os.pojo.Report reportPo, Integer dataType, String areaId) {
		buildReportTableHead(sheet, reportTemplateInfo, styles);
		buildReportTableBodyNew(sheet, reportTemplateInfo, styles, reportPo, dataType, areaId);
	}

	/**
	 * 构造表格表体部分.
	 * 
	 * @param sheet
	 * @param reportTemplateInfo
	 */
	private void buildReportTableBody(Sheet sheet, Report reportTemplateInfo, Map<String, CellStyle> styles) {
		Table<Integer, Integer, ReportBodyCell> bodyTable = TreeBasedTable.create();
		for (ReportBodyCell cell : reportTemplateInfo.getUniqueReportTableBodys()) {
			bodyTable.put(cell.getRowid(), cell.getColid(), cell);
		}

		// Map<Integer, Integer> rowHeights = getRowHeights(report);
		for (Integer rowKey : bodyTable.rowKeySet()) {
			Map<Integer, ReportBodyCell> rowMap = bodyTable.row(rowKey);
			Row row = sheet.createRow(rowKey);
			row.setHeightInPoints(DEFAULT_ROW_HEIGHT);
			for (Entry<Integer, ReportBodyCell> entry : rowMap.entrySet()) {
				ReportBodyCell cell = entry.getValue();
				Cell excelCell = row.createCell(cell.getColid());

				if (ReportBodyCell.CELL_TYPE_INPUT.equals(cell.getType())) {
					excelCell.setCellStyle(styles.get(INDICATOR_STYLE_NAME));
					// excelCell.setCellValue("");
				} else if (ReportCell.CELL_TYPE_STATIC.equals(cell.getType())) {
					excelCell.setCellStyle(styles.get(TEXT_STYLE_NAME));
					String cellStr = cell.getContent().replaceAll("&nbsp;", " ");
					if (cell.getIndentlevel() > 0) {
						cellStr = StringUtils.repeat(" ", 4) + cellStr;
					}
					excelCell.setCellValue(cellStr);
				} else {
					excelCell.setCellStyle(styles.get(TEXT_STYLE_NAME));
					excelCell.setCellValue("");
				}
			}
		}

	}

	public Set<String> queryIndicatorsByArea(String areaId) {
		String sql = "select * from v_indicator_info vii where EXISTS(select tui.id from tbl_unit_indicator tui where exists( select tu.id from tbl_unit tu where tu.unit_area_id=@areaId and tui.unit_id=tu.id) and tui.indicator_id=vii.id)";
		HashMap map = new HashMap();
		map.put("areaId", areaId);
		List<IndicatorInfo> query = new ArrayList<IndicatorInfo>();
		query = sqlTemplate.query(sql, map, dao.getEntity(IndicatorInfo.class));
		Set<String> indicators = new HashSet<String>();
		if (query != null && query.size() > 0) {
			for (IndicatorInfo info : query)
				indicators.add(info.getId().toString());

		}
		return indicators;
	}

	/**
	 * 构造表格表体部分.
	 * 
	 * @param sheet
	 * @param reportTemplateInfo
	 */
	private void buildReportTableBodyNew(Sheet sheet, ReportTemplateInfo reportTemplateInfo,
			Map<String, CellStyle> styles, cn.com.generaldata.os.pojo.Report reportPo, Integer dataType, String areaId) {
		Table<Integer, Integer, ReportBodyCell> bodyTable = TreeBasedTable.create();
		//取得该报表所有指标
		List<Integer> indicatorList = new ArrayList<Integer>();
		for (ReportBodyCell cell : reportTemplateInfo.getUniqueReportTableBodys()) {
			bodyTable.put(cell.getRowid(), cell.getColid(), cell);
			if (ReportBodyCell.CELL_TYPE_INPUT.equals(cell.getType())) {
				indicatorList.add(cell.getIndicatorid());
			}
		}
		Cnd cnd = Cnd.where("reportTypeId", "=", reportTemplateInfo.getReportTypeId()).and("indicatorId", "in",
				indicatorList);

		List<ReportTypeIndicator> query = dao.query(ReportTypeIndicator.class, cnd);
		Set<ReportDetail> reportDetails = reportPo.getReportDetails();
		//屏蔽指标
		//		Set<String> indicatorsByArea = queryIndicatorsByArea(areaId);
		//单元格式
		if (dataType == 1) {
			// Map<Integer, Integer> rowHeights = getRowHeights(report);
			for (Integer rowKey : bodyTable.rowKeySet()) {
				Map<Integer, ReportBodyCell> rowMap = bodyTable.row(rowKey);
				Row row = sheet.createRow(rowKey);
				row.setHeightInPoints(DEFAULT_ROW_HEIGHT);
				for (Entry<Integer, ReportBodyCell> entry : rowMap.entrySet()) {
					ReportBodyCell cell = entry.getValue();
					Cell excelCell = row.createCell(cell.getColid());
					if (ReportBodyCell.CELL_TYPE_INPUT.equals(cell.getType())) {
						excelCell.setCellStyle(styles.get(INDICATOR_STYLE_NAME));
						Integer indicatorid = cell.getIndicatorid();
						Indicator indicator = dao.fetch(Indicator.class, indicatorid);
						for (ReportTypeIndicator rti : query) {
							if (indicatorid.equals(rti.getIndicatorId())) {
								Integer row2 = rti.getRow();
								Integer theColumn = rti.getTheColumn();
								for (ReportDetail rd : reportDetails) {
									if (row2.equals(Integer.valueOf(rd.getRowIndex()))) {
										String fieldName = "f" + theColumn;
										Object value = mirrorReportDetail.getValue(rd, fieldName);
										String strVal = Castors.me().castTo(value == null ? "" : value, String.class);
										//										if (indicatorsByArea.contains(indicatorid.toString()))
										//											strVal = "";
										if (1 == indicator.getDataType()) {
											Double all = 0d;
											if (StringUtils.isNotBlank(strVal))
												all = Double.valueOf(strVal);
											excelCell.setCellType(HSSFCell.CELL_TYPE_NUMERIC);
											excelCell.setCellValue(all);
										} else
											excelCell.setCellValue(strVal);
										break;
									}
								}
								break;
							}
						}
					} else if (ReportCell.CELL_TYPE_STATIC.equals(cell.getType())) {
						excelCell.setCellStyle(styles.get(TEXT_STYLE_NAME));
						String cellStr = cell.getContent().replaceAll("&nbsp;", " ");
						if (cell.getIndentlevel() > 0) {
							cellStr = StringUtils.repeat(" ", 4) + cellStr;
						}
						excelCell.setCellValue(cellStr);
					} else {
						excelCell.setCellStyle(styles.get(TEXT_STYLE_NAME));
						excelCell.setCellValue("");
					}
				}
			}
		} else if (dataType == 2) {// Map<Integer, Integer> rowHeights = getRowHeights(report);
			for (ReportDetail rd : reportDetails) {
				Integer rowKey = 0;
				for (Integer rowKeyValue : bodyTable.rowKeySet()) {
					rowKey = rowKeyValue;
					break;
				}
				Map<Integer, ReportBodyCell> rowMap = bodyTable.row(rowKey);
				Row row = sheet.createRow(rowKey + rd.getRowIndex());
				row.setHeightInPoints(DEFAULT_ROW_HEIGHT);
				for (Entry<Integer, ReportBodyCell> entry : rowMap.entrySet()) {
					ReportBodyCell cell = entry.getValue();
					Cell excelCell = row.createCell(cell.getColid());

					if (ReportBodyCell.CELL_TYPE_INPUT.equals(cell.getType())) {
						excelCell.setCellStyle(styles.get(INDICATOR_STYLE_NAME));
						Integer indicatorid = cell.getIndicatorid();
						Indicator indicator = dao.fetch(Indicator.class, indicatorid);
						for (ReportTypeIndicator rti : query) {
							if (indicatorid.equals(rti.getIndicatorId())) {
								Integer theColumn = rti.getTheColumn();
								String fieldName = "f" + (theColumn);
								Object value = mirrorReportDetail.getValue(rd, fieldName);
								String strVal = Castors.me().castTo(value == null ? "" : value, String.class);
								//								if (indicatorsByArea.contains(indicatorid.toString()))
								//									strVal = "";
								if (1 == indicator.getDataType()) {
									Double all = 0d;
									if (StringUtils.isNotBlank(strVal))
										all = Double.valueOf(strVal);
									excelCell.setCellType(HSSFCell.CELL_TYPE_NUMERIC);
									excelCell.setCellValue(all);
								} else
									excelCell.setCellValue(strVal);
								break;
							}
						}
					} else if (ReportCell.CELL_TYPE_STATIC.equals(cell.getType())) {
						excelCell.setCellStyle(styles.get(TEXT_STYLE_NAME));
						String cellStr = cell.getContent().replaceAll("&nbsp;", " ");
						if (cell.getIndentlevel() > 0) {
							cellStr = StringUtils.repeat(" ", 4) + cellStr;
						}
						excelCell.setCellValue(cellStr);
					} else {
						excelCell.setCellStyle(styles.get(TEXT_STYLE_NAME));
						excelCell.setCellValue("");
					}
				}
			}
		}
	}

	/**
	 * 构造表格表头部分
	 * 
	 * @param sheet
	 * @param reportTemplateInfo
	 */
	private void buildReportTableHead(Sheet sheet, ReportTemplateInfo reportTemplateInfo, Map<String, CellStyle> styles) {

		Table<Integer, Integer, ReportTitleCell> headTable = TreeBasedTable.create();
		for (ReportTitleCell cell : reportTemplateInfo.getUniqueReportTableHeads()) {
			headTable.put(cell.getRowid(), cell.getColid(), cell);
		}

		// Map<Integer, Integer> rowHeights = getRowHeights(report);
		for (Integer rowKey : headTable.rowKeySet()) {
			Map<Integer, ReportTitleCell> rowMap = headTable.row(rowKey);
			Row row = sheet.createRow(rowKey);
			row.setHeightInPoints(DEFAULT_ROW_HEIGHT);
			for (Entry<Integer, ReportTitleCell> entry : rowMap.entrySet()) {
				ReportTitleCell cell = entry.getValue();
				Cell excelCell = row.createCell(cell.getColid());
				excelCell.setCellStyle(styles.get(HEAD_STYLE_NAME));

				if (ReportCell.CELL_TYPE_NONE.equals(cell.getType())) {
					excelCell.setCellValue("");
				} else if (ReportCell.CELL_TYPE_STATIC.equals(cell.getType())) {
					excelCell.setCellValue(cell.getContent().replaceAll("&nbsp;", " "));
				}
			}
		}
	}

	/**
	 * 构造表格表头部分
	 * 
	 * @param sheet
	 * @param report
	 */
	private void buildReportTableHead(Sheet sheet, Report report, Map<String, CellStyle> styles) {

		Table<Integer, Integer, ReportTitleCell> headTable = TreeBasedTable.create();
		for (ReportTitleCell cell : report.getUniqueReportTableHeads()) {
			headTable.put(cell.getRowid(), cell.getColid(), cell);
		}

		// Map<Integer, Integer> rowHeights = getRowHeights(report);
		for (Integer rowKey : headTable.rowKeySet()) {
			Map<Integer, ReportTitleCell> rowMap = headTable.row(rowKey);
			Row row = sheet.createRow(rowKey);
			row.setHeightInPoints(DEFAULT_ROW_HEIGHT);
			for (Entry<Integer, ReportTitleCell> entry : rowMap.entrySet()) {
				ReportTitleCell cell = entry.getValue();
				Cell excelCell = row.createCell(cell.getColid());
				excelCell.setCellStyle(styles.get(HEAD_STYLE_NAME));

				if (ReportCell.CELL_TYPE_NONE.equals(cell.getType())) {
					excelCell.setCellValue("");
				} else if (ReportCell.CELL_TYPE_STATIC.equals(cell.getType())) {
					excelCell.setCellValue(cell.getContent().replaceAll("&nbsp;", " "));
				}
			}
		}
	}

	private void mergeCellsNew(Sheet sheet, ReportTemplateInfo reportTemplateInfo,
			cn.com.generaldata.os.pojo.Report reportPo, Integer dataType) {
		for (ReportTitleCell cell : reportTemplateInfo.getUniqueReportTitles()) {
			mergeCell(sheet, cell);
		}
		for (ReportCell cell : reportTemplateInfo.getUniqueReportTableHeads()) {
			mergeCell(sheet, cell);
		}
		for (ReportBodyCell cell : reportTemplateInfo.getUniqueReportTableBodys()) {
			mergeCellBodyNew(sheet, cell, reportPo, dataType);
		}
		for (ReportFooterCell cell : reportTemplateInfo.getUniqueReportFoots()) {
			mergeCellFootNew(sheet, cell, reportPo, dataType);
		}
	}

	private void mergeCells(Sheet sheet, Report reportTemplateInfo) {
		for (ReportTitleCell cell : reportTemplateInfo.getUniqueReportTitles()) {
			mergeCell(sheet, cell);
		}
		for (ReportCell cell : reportTemplateInfo.getUniqueReportTableHeads()) {
			mergeCell(sheet, cell);
		}
		for (ReportBodyCell cell : reportTemplateInfo.getUniqueReportTableBodys()) {
			mergeCell(sheet, cell);
		}
		for (ReportFooterCell cell : reportTemplateInfo.getUniqueReportFoots()) {
			mergeCell(sheet, cell);
		}
	}

	private void mergeCellFootNew(Sheet sheet, ReportCell cell, cn.com.generaldata.os.pojo.Report reportPo,
			Integer dataType) {

		if (cell.getRowSpan() > 1 || cell.getColSpan() > 1) {
			int firstRow = cell.getRowid(), lastRow = cell.getRowid();
			if (dataType == 2) {
				Set<ReportDetail> reportDetails = reportPo.getReportDetails();
				firstRow = cell.getRowid() + reportDetails.size() - 1;
				lastRow = cell.getRowid() + reportDetails.size() - 1;
			}
			int firstColumn = cell.getColid(), lastColumn = cell.getColid();
			if (cell.getRowSpan() > 1) {
				lastRow = lastRow + cell.getRowSpan() - 1;
			}
			if (cell.getColSpan() > 1) {
				lastColumn = lastColumn + cell.getColSpan() - 1;
			}
			sheet.addMergedRegion(new CellRangeAddress(firstRow, lastRow, firstColumn, lastColumn));
		}

	}

	private void mergeCellBodyNew(Sheet sheet, ReportCell cell, cn.com.generaldata.os.pojo.Report reportPo,
			Integer dataType) {

		if (cell.getRowSpan() > 1 || cell.getColSpan() > 1) {
			if (dataType == 2) {
				Set<ReportDetail> reportDetails = reportPo.getReportDetails();
				for (ReportDetail rd : reportDetails) {
					int firstRow = cell.getRowid() + rd.getRowIndex(), lastRow = cell.getRowid() + rd.getRowIndex();
					int firstColumn = cell.getColid(), lastColumn = cell.getColid();
					if (cell.getRowSpan() > 1) {
						lastRow = lastRow + cell.getRowSpan() - 1;
					}
					if (cell.getColSpan() > 1) {
						lastColumn = lastColumn + cell.getColSpan() - 1;
					}
					sheet.addMergedRegion(new CellRangeAddress(firstRow, lastRow, firstColumn, lastColumn));
				}
			} else {
				int firstRow = cell.getRowid(), lastRow = cell.getRowid();
				int firstColumn = cell.getColid(), lastColumn = cell.getColid();
				if (cell.getRowSpan() > 1) {
					lastRow = lastRow + cell.getRowSpan() - 1;
				}
				if (cell.getColSpan() > 1) {
					lastColumn = lastColumn + cell.getColSpan() - 1;
				}
				sheet.addMergedRegion(new CellRangeAddress(firstRow, lastRow, firstColumn, lastColumn));
			}
		}

	}

	private void mergeCell(Sheet sheet, ReportCell cell) {
		if (cell.getRowSpan() > 1 || cell.getColSpan() > 1) {
			int firstRow = cell.getRowid(), lastRow = cell.getRowid();
			int firstColumn = cell.getColid(), lastColumn = cell.getColid();
			if (cell.getRowSpan() > 1) {
				lastRow = lastRow + cell.getRowSpan() - 1;
			}
			if (cell.getColSpan() > 1) {
				lastColumn = lastColumn + cell.getColSpan() - 1;
			}
			sheet.addMergedRegion(new CellRangeAddress(firstRow, lastRow, firstColumn, lastColumn));
		}

	}

	private void buildReportHead(Sheet sheet, Report reportTemplateInfo) {
		ReportTypeNz reportType = dao.fetch(ReportTypeNz.class, reportTemplateInfo.getReportTypeId());
		Table<Integer, Integer, ReportTitleCell> headTable = TreeBasedTable.create();
		for (ReportTitleCell cell : reportTemplateInfo.getUniqueReportTitles()) {
			headTable.put(cell.getRowid(), cell.getColid(), cell);
		}

		// Map<Integer, Integer> rowHeights = getRowHeights(report);
		for (Integer rowKey : headTable.rowKeySet()) {
			Map<Integer, ReportTitleCell> rowMap = headTable.row(rowKey);
			Row row = sheet.createRow(rowKey);
			row.setHeightInPoints(DEFAULT_ROW_HEIGHT);
			for (Entry<Integer, ReportTitleCell> entry : rowMap.entrySet()) {
				ReportTitleCell cell = entry.getValue();
				Cell excelCell = row.createCell(cell.getColid());

				if (ReportCell.CELL_TYPE_NONE.equals(cell.getType())) {
					excelCell.setCellValue("");
				} else if (ReportCell.CELL_TYPE_STATIC.equals(cell.getType())) {
					excelCell.setCellValue(cell.getContent().replaceAll("&nbsp;", " "));
				} else if (ReportCell.CELL_TYPE_INPUT.equals(cell.getType())) {
					setTitleExpCellVal(cell, reportType, excelCell);
				}
			}
		}

	}

	private void buildReportHeadNew(Sheet sheet, ReportTemplateInfo reportTemplateInfo,
			cn.com.generaldata.os.pojo.Report reportNz) {
		ReportTypeNz reportType = dao.fetch(ReportTypeNz.class, reportTemplateInfo.getReportTypeId());
		Table<Integer, Integer, ReportTitleCell> headTable = TreeBasedTable.create();
		for (ReportTitleCell cell : reportTemplateInfo.getUniqueReportTitles()) {
			headTable.put(cell.getRowid(), cell.getColid(), cell);
		}

		// Map<Integer, Integer> rowHeights = getRowHeights(report);
		for (Integer rowKey : headTable.rowKeySet()) {
			Map<Integer, ReportTitleCell> rowMap = headTable.row(rowKey);
			Row row = sheet.createRow(rowKey);
			row.setHeightInPoints(DEFAULT_ROW_HEIGHT);
			for (Entry<Integer, ReportTitleCell> entry : rowMap.entrySet()) {
				ReportTitleCell cell = entry.getValue();
				Cell excelCell = row.createCell(cell.getColid());

				if (ReportCell.CELL_TYPE_NONE.equals(cell.getType())) {
					excelCell.setCellValue("");
				} else if (ReportCell.CELL_TYPE_STATIC.equals(cell.getType())) {
					excelCell.setCellValue(cell.getContent().replaceAll("&nbsp;", " "));
				} else if (ReportCell.CELL_TYPE_INPUT.equals(cell.getType())) {
					setTitleExpCellValNew(cell, reportType, excelCell, reportNz);
				}
			}
		}

	}

	/**
	 * 设置报表表头区域表达式单元格值.
	 * 
	 * @param cell
	 * @param reportType
	 * @param excelCell
	 */
	private void setTitleExpCellValNew(ReportCell cell, ReportTypeNz reportType, Cell excelCell,
			cn.com.generaldata.os.pojo.Report reportNz) {
		if (cell.getInputid() == null) {
			return;
		}
		ReportInputTitle reportInputTitle = dao.fetch(ReportInputTitle.class, cell.getInputid());
		if (reportInputTitle.getExp().contains("reportType")) {
			Object inputVal = mirror.getValue(reportType, reportInputTitle.getAttrName());
			String strVal = Castors.me().castTo(inputVal == null ? "" : inputVal, String.class);
			strVal = StringUtils.defaultString(strVal);
			// 全是数字，表示为批准机关，制表机关代码
			if (strVal.matches("\\d")) {
				strVal = unitCodeMap.get(strVal);
			}
			excelCell.setCellValue(strVal);
		} else {
			try {
				Object inputVal = null;
				if (reportInputTitle.getExp().contains("user")) {
					Context context = Lang.context();
					context.set("user", reportNz.getUser());
					inputVal = El.eval(context, reportInputTitle.getAttrName());
				} else if (reportInputTitle.getExp().contains("reportTime"))
					inputVal = mirrorReport.getValue(reportNz, "reportMonth");
				else
					inputVal = mirrorReport.getValue(reportNz, reportInputTitle.getAttrName());
				String strVal = Castors.me().castTo(inputVal == null ? "" : inputVal, String.class);
				strVal = StringUtils.defaultString(strVal);
				if (reportInputTitle.getExp().contains("reportDate"))
					strVal = formatter.format(formatter.parse(strVal));
				// 全是数字，表示为批准机关，制表机关代码
				if (strVal.matches("\\d")) {
					strVal = unitCodeMap.get(strVal);
				}
				excelCell.setCellValue(strVal);
			} catch (Exception e) {
				// TODO Auto-generated catch block
				logger.error("映射属性错误", e);
			}
		}

	}

	/**
	 * 设置报表表头区域表达式单元格值.
	 * 
	 * @param cell
	 * @param reportType
	 * @param excelCell
	 */
	private void setTitleExpCellVal(ReportTitleCell cell, ReportTypeNz reportType, Cell excelCell) {
		if (cell.getInputid() == null) {
			return;
		}
		ReportInputTitle reportInputTitle = dao.fetch(ReportInputTitle.class, cell.getInputid());
		if (reportInputTitle.getType().intValue() == ReportInputTitle.TYPE_DISPLAY) {
			if (reportInputTitle.getExp().contains("reportType")) {
				Object inputVal = mirror.getValue(reportType, reportInputTitle.getAttrName());
				String strVal = Castors.me().castTo(inputVal == null ? "" : inputVal, String.class);
				strVal = StringUtils.defaultString(strVal);
				// 全是数字，表示为批准机关，制表机关代码
				if (strVal.matches("\\d")) {
					strVal = unitCodeMap.get(strVal);
				}
				excelCell.setCellValue(strVal);
			}
		}
	}

	/**
	 * 创建单元格样式
	 * 
	 * @param wb
	 * @return
	 */
	private Map<String, CellStyle> createStyles(Workbook wb) {
		Map<String, CellStyle> styles = Maps.newHashMap();
		// --字体设定 --//

		// 普通字体
		Font normalFont = wb.createFont();
		normalFont.setFontName("宋体");
		normalFont.setFontHeightInPoints((short) 10);

		// 加粗字体
		Font boldFont = wb.createFont();
		boldFont.setFontName("宋体");
		boldFont.setFontHeightInPoints((short) 10);
		boldFont.setBoldweight(Font.BOLDWEIGHT_BOLD);

		// 标题格式
		CellStyle headerStyle = wb.createCellStyle();
		headerStyle.setFont(boldFont);
		headerStyle.setAlignment(CellStyle.ALIGN_CENTER);
		headerStyle.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
		headerStyle.setFillForegroundColor(IndexedColors.GREY_40_PERCENT.getIndex());
		headerStyle.setFillPattern(CellStyle.SOLID_FOREGROUND);
		// setBorder(headerStyle);
		styles.put(HEAD_STYLE_NAME, headerStyle);

		// 表格普通字体样式
		CellStyle textStyle = wb.createCellStyle();
		textStyle.setFont(normalFont);
		textStyle.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
		textStyle.setFillPattern(CellStyle.SOLID_FOREGROUND);
		// setBorder(textStyle);
		styles.put(TEXT_STYLE_NAME, textStyle);

		// 输入指标样式
		CellStyle indicatorStyle = wb.createCellStyle();
		indicatorStyle.setFont(normalFont);
		// setBorder(indicatorStyle);
		styles.put(INDICATOR_STYLE_NAME, indicatorStyle);

		return styles;

	}

	private void setBorder(CellStyle style) {
		// 设置边框
		style.setBorderRight(CellStyle.BORDER_THIN);
		style.setRightBorderColor(IndexedColors.BLACK.getIndex());

		style.setBorderLeft(CellStyle.BORDER_THIN);
		style.setLeftBorderColor(IndexedColors.BLACK.getIndex());

		style.setBorderTop(CellStyle.BORDER_THIN);
		style.setTopBorderColor(IndexedColors.BLACK.getIndex());

		style.setBorderBottom(CellStyle.BORDER_THIN);
		style.setBottomBorderColor(IndexedColors.BLACK.getIndex());
	}

	/**
	 * 获取报表设计设定每列宽度.
	 * 
	 * @param reportTemplateInfo
	 * @return
	 */
	private int[] getColumnWidths(ReportTemplateInfo reportTemplateInfo) {
		SortedMultiset<Integer> cellCounts = TreeMultiset.create();
		Multimap<Integer, ReportCell> rowCells = ArrayListMultimap.create();

		for (ReportCell cell : reportTemplateInfo.getUniqueReportCells()) {
			cellCounts.add(cell.getRowid());
			rowCells.put(cell.getRowid(), cell);
		}

		int[] widths = new int[cellCounts.count(cellCounts.lastEntry().getElement())];
		for (ReportCell cell : rowCells.get(cellCounts.lastEntry().getElement())) {
			int width = 0;
			if (cell.getCellStyle().getWidth() == 0) {
				width = DEFAULT_COLUMN_WIDTH;
			} else {
				width = cell.getCellStyle().getWidth();
			}

			widths[cell.getColid()] = width;
		}

		return widths;
	}

	/**
	 * 获取每行的高度.
	 * 
	 * @param reportTemplateInfo
	 * @return
	 */
	private Map<Integer, Integer> getRowHeights(ReportTemplateInfo reportTemplateInfo) {

		Map<Integer, Integer> rowHeightMap = ctx.get();
		if (rowHeightMap == null) {
			rowHeightMap = Maps.newHashMap();
			for (ReportCell cell : reportTemplateInfo.getUniqueReportCells()) {
				int height = 0;
				if (cell.getCellStyle().getHeight() == 0) {
					height = DEFAULT_ROW_HEIGHT;
				} else {
					height = cell.getCellStyle().getHeight();
				}

				if (!rowHeightMap.containsKey(cell.getRowid())) {
					rowHeightMap.put(cell.getRowid(), height);
				}
			}
			ctx.set(rowHeightMap);
		}

		return rowHeightMap;
	}
}
